﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_Mirroring Failover (ODBC)</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:02/12/2008 21:30:29-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_Mirroring Failover (ODBC)</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 2/12/2008 9:30:29 PM</p>
        
        <font color="DarkGray">[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.] </font><p /> 
        <span id="changeHistory">
        </span>
    <p>
      This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
    </p>
    <p>This sample demonstrates the mirroring failover feature in SQL Server. This sample is not supported using SQL Server Express.</p>
    <p>
      SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see <a href="html/e8117979-fe03-441e-be85-894fa616227a.htm">Considerations for Installing SQL Server Samples and Sample Databases</a>.</p>
  <h1 class="heading">Languages</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">This sample uses Visual C++.</p>
    </content></div><h1 class="heading">Prerequisites</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Before running this sample, make sure the following software is installed:</p>
      <ul xmlns=""><li>
          
            SQL Server including Database Engine. <br />
        </li><li>
          The SQL Server Database Engine samples. These samples are included with SQL Server. You can download the latest version of the samples at the <a href="http://go.microsoft.com/fwlink/?linkid=62796" alt=""><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Microsoft SQL Server Developer Center</linkText></a>.<br />
        </li><li>
          
            .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See <b>Installing the .NET Framework SDK</b>. You will need to modify the INCLUDE environment variable to include the  \Microsoft SQL Server\100\SDK\Include directory (if you are using Microsoft Visual Studio 2005, you can do this in the development environment; see <a href="http://go.microsoft.com/fwlink/?LinkId=68318" alt=""><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">VC++ Directories, Projects and Solutions, Options Dialog Box</linkText></a> for more information).<br />
        </li></ul>
      <p xmlns="">You will also need to configure a principal database mirror database for the principal database; for more information, see <b>How to: Set Large Data (OLE DB)</b> and <b>Troubleshooting Database Mirroring Deployment</b>.</p>
      <p xmlns="">If you prefer, you can enable database mirroring on your machine with the following scripts. These scripts can be executed within SQL Server Management Studio or by using SQLCMD at the command prompt.</p>
    </content><sections xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
        </content><sections xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <h4 class="subHeading" xmlns="">Ensure Database and Endpoints Used in This Sample Do Not Exist on the Principal Server Instance</h4><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">The following script will ensure that the principal server does not have a database or endpoint name used by the sample.</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>-- Step 1 (on Principal)
-- Return primary server instance to a premirroring state
-- 
ALTER DATABASE HADB SET PARTNER OFF
DROP DATABASE HADB;
DROP ENDPOINT Mirroring;
EXEC sys.sp_dbmmonitordropmonitoring;</pre></td></tr></table></span></div>
            </content></div>
          <h4 class="subHeading" xmlns="">Ensure Database and Endpoints Used in This Sample Do Not Exist on the Mirror Server Instance</h4><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">The following script will ensure that the mirror server instance does not have a database or endpoint name used by the sample.</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>-- Step 2 (on Mirror)
-- Return mirror server instance to a premirroring state
-- 
ALTER DATABASE HADB SET PARTNER OFF
RESTORE DATABASE HADB WITH RECOVERY;
DROP DATABASE HADB;
DROP ENDPOINT Mirroring;</pre></td></tr></table></span></div>
            </content></div>
          <h4 class="subHeading" xmlns="">Create Database and Database Backups</h4><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">This script, which is run on the principal server instance, creates the database and database backups.</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>-- Step 3 (on Principal)
-- create db and backups
-- 
USE master;
GO
CREATE DATABASE HADB; 
ALTER DATABASE HADB SET RECOVERY FULL; 

-- CREATE BACKUPS
BACKUP DATABASE HADB 
    TO DISK = 'C:\HADB.bak'
    WITH FORMAT;
GO

BACKUP LOG HADB
   TO DISK = 'C:\HADB.bak';</pre></td></tr></table></span></div>
            </content></div>
          <h4 class="subHeading" xmlns="">Create the Mirror Database</h4><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">This script, which is run on the server instance that will contain the mirror database, creates the mirror database from the backup created in the previous script. If the mirror server and the principal server are not on the same physical machine, you will need to copy c:\hadb.bak from the principal server machine to the mirror server machine.</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>-- Step 4 (on Mirror)
-- Create the mirror database
RESTORE DATABASE HADB
FROM DISK='C:\HADB.bak'
WITH NORECOVERY, FILE=1,
MOVE 'HADB' TO 'C:\HADB.mdf',
MOVE 'HADB_log' TO 'C:\HADB_log.ldf'
;

RESTORE LOG HADB
FROM DISK='C:\HADB.bak'
WITH NORECOVERY, FILE=2,
MOVE 'HADB' TO 'C:\HADB.mdf',
MOVE 'HADB_log' TO 'C:\HADB_log.ldf'
;</pre></td></tr></table></span></div>
            </content></div>
          <h4 class="subHeading" xmlns="">Create Mirror Endpoint on the Principal Server</h4><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">This script, run on the principal server instance, creates a mirroring endpoint.</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>-- Step 5 (on Principal)
-- create mirroring endpoint on PRINCIPAL server
-- 
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP(LISTENER_PORT = 7022)
FOR database_mirroring (ROLE = PARTNER);</pre></td></tr></table></span></div>
            </content></div>
          <h4 class="subHeading" xmlns="">Create Mirror Endpoint on the Mirror Server Instance</h4><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">This script, run on the mirror server instance, creates a mirroring endpoint.</p>
              <p xmlns="">You can discover your machine name and domain name (full computer name) by looking at the properties of your computer (on Windows XP, right-click <b>My Computer</b>, click <b>Properties</b>, and look on the <b>Computer Name</b> tab).</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>-- Step 6 (on MIRROR)
-- create mirroring endpoint
-- 
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP(LISTENER_PORT = 7023)
FOR database_mirroring (ROLE = PARTNER);

-- also on mirror, set partner to PRINCIPAL server 
--(This must be done on mirror first.)
ALTER DATABASE HADB SET PARTNER='tcp://machine_name.domain:7022';</pre></td></tr></table></span></div>
            </content></div>
          <h4 class="subHeading" xmlns="">Set Principal to the Mirror Server</h4><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">This script, run on the principal server, specifies the mirror database. You may see a failure at this point if the principal server has a firewall that prevents connections on port 7022. In this case, make an exception in your firewall for this port number.</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>-- Step 7 (on Principal)
-- Set partner to the mirror server on port 7023
-- 
ALTER DATABASE HADB SET PARTNER='tcp://machine_name.domain:7023';

-- start collecting mirroring history:
USE master;
EXEC sys.sp_dbmmonitoraddmonitoring;</pre></td></tr></table></span></div>
            </content></div>
        </sections></div>
    </sections></div><h1 class="heading">Building the Sample</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">To build the sample, open Failover.sln in Visual Studio (CTRL+SHIFT+O) and click <b>Build Solution</b> on the <b>Build</b> menu, or invoke MSBUILD from a command prompt on the solution.</p>
    </content></div><h1 class="heading">Running the Sample</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <ul xmlns=""><li>
          From Visual Studio, invoke <b>Start Without Debugging</b> (CTRL+F5).<br />
        </li><li>
          If you built with MSBuild.exe, invoke Failover.exe.<br />
        </li><li>
          Change the Principal Server and Principal Server in the user interface to your server names.<br />
        </li><li>
          Click <b>Init Table</b>. This creates a simple table with a column used as an update counter.<br />
        </li><li>
          Click <b>Connect</b>.<br />
        </li><li>
          Click <b>Update</b> to update the table, and click <b>Commit</b> to commit the changes to your database.<br />
        </li><li>
          To force manual failover, click <b>Failover</b>.<br />
        </li></ul>
    </content></div><h1 class="heading">Remarks</h1><div id="sectionSection4" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">You can now click <b>Connect</b>, <b>Update</b>, <b>Commit</b>, and <b>Disconnect</b>. The <b>Count</b> field increments each time you click <b>Update</b>. If you click <b>Failover</b> with uncommitted updates, disconnect and then reconnect you will see the update state after your last commit. When you disconnect, you'll see an error and the connection and the current transaction will be rolled back after failover happen.</p>
      <p xmlns="">An application can detect which server it connects to and the server's partner. You can do this by selecting the <b>Track Changes</b> check box. The advantage of doing this is that an application that updates its connection string is more likely to go to the active server directly on startup, which is a marginal gain, and can have zero long-term maintenance costs. Suppose A is mirrored by B and A fails, causing B to become the active server. Now a new server C is introduced as a mirror to B. An application requesting A as principal and B as mirror would fail to connect when B fails over to C.</p>
      <p xmlns="">Click <b>Continuous Test</b> and observe the counter. Now click <b>Failover</b> at intervals. The time to failover is displayed (look at the code to see how this is measured). Also, the code demonstrates simple high level failover logic. That is, assume the current transaction is lost, make a small number of reconnection attempts (three in the demonstration) and repeat the failed transaction, or report it to the user.</p>
      <p xmlns="">There are buttons to get a current client setting for the alias ActiveServer, and to set this alias to the current active server. If you use an alias with scripts and utilities then you can use SMO to update the alias after failover. Now utilities and scripts will connect to the new active server. </p>
    </content></div><h1 class="heading">See Also</h1><div id="seeAlsoSection" class="section" name="collapseableSection"><h4 class="subHeading">Concepts</h4><a href="html/b62ad48e-7798-4734-b2e0-d457f2566742.htm">Data Access Samples</a><br /><br />
		<h4 class="subHeading">
			Help and Information
		</h4>
		<b>
			
			Getting SQL Server 2008 Assistance
		</b>
	</div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>